package com.tuling.dao;

import com.tuling.Util.DBUtils;
import com.tuling.Util.DruidUtils;
import com.tuling.domain.User;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

public class UserDaoImpl implements UserDao {

    private QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
    @Override
    public int insert(User user) {
        String sql = "insert into user(email,phone,pwd,uname) values(?,?,?,?)";
        Object[] params = {user.getEmail(), user.getPhone(), user.getPwd(), user.getUname()};
        try {
            int update = queryRunner.update(sql, params);
            return update;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        /*// 数据库连接
//        Connection connection = DBUtils.getConnection();
        Connection connection = DruidUtils.getConnection();
        PreparedStatement preparedStatement = null;
        try {// 获取执行sql语句的对象
            String sql = "insert into user(email,phone,pwd,uname) values(?,?,?,?)";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, user.getEmail());
            preparedStatement.setString(2, user.getPhone());
            preparedStatement.setString(3, user.getPwd());
            preparedStatement.setString(4, user.getUname());
            int i = preparedStatement.executeUpdate();
            return i;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
//            DBUtils.closeAll(connection, preparedStatement, null);
            DBUtils.closeAll(connection, preparedStatement, null);
        }*/
        return 0;
    }

    @Override
    public User findByStr(String str) {
        String sql = "select * from user where email = ? or phone = ?";
        Object[] params = {str, str};
        try {
            User query = queryRunner.query(sql, new BeanHandler<User>(User.class), params);
            return query;
        } catch (SQLException e) {
            e.printStackTrace();
        }
//        Connection connection = DBUtils.getConnection();
        /*Connection connection = DruidUtils.getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try{
            String sql = "select * from user where email = ? or phone = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, str);
            preparedStatement.setString(2, str);
            resultSet = preparedStatement.executeQuery();
            User user = new User();
            if (resultSet.next()) {
                user.setEmail(resultSet.getString("email"));
                user.setId(resultSet.getInt("id"));
                user.setPhone(resultSet.getString("phone"));
                user.setPwd(resultSet.getString("pwd"));
                user.setUname(resultSet.getString("uname"));
            }
            return user;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DruidUtils.closeAll(connection, preparedStatement, resultSet);
        }*/
        return null;
    }

    @Override
    public List<User> findALl() {
        String sql = "select * from user";
        try {
            List<User> query = queryRunner.query(sql, new BeanListHandler<User>(User.class));
            return query;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public User findById(int id) {
        String sql = "select * from user where id = ?";
        Object[] params = {id};
        try {
            User query = queryRunner.query(sql, new BeanHandler<>(User.class), params);
            return query;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public int update(User user) {
        String sql = "update user set email = ?, phone = ?, pwd = ?, uname = ? where id =?";
        Object[] params = {user.getEmail(), user.getPhone(), user.getPwd(), user.getPwd(), user.getId()};
        try {
            int update = queryRunner.update(sql, params);
            return update;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public int del(int id) {
        String sql = "DELETE FROM `user` WHERE id =?";
        Object[] params = {id};
        try {
            int update = queryRunner.update(sql, params);
            return update;
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return 0;
    }
}
